/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sqlserver;

/**
 *
 * @author ROMANCE
 */
import java.util.ArrayList;
import java.util.List;
import java.util.StringTokenizer;

import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathExpression;
import javax.xml.xpath.XPathFactory;

import org.w3c.dom.Attr;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

public class Create {
    public static int execute_syntax_command (String command, String dbName)
	{
            StringTokenizer st=new StringTokenizer(command.toLowerCase()," ,");
            int state=0;//0=select;1=from;2=where
            ArrayList<String> create=new ArrayList<String>();
            ArrayList<String> table=new ArrayList<String>();
            while (st.hasMoreTokens()){
                  String theToken=st.nextToken();
                  System.out.println("Token:"+theToken);
                  //here you can check if you are in select zone, from zone, or where zone or...
                  //and you can decide what you want to do
                  if (theToken.equals("create"))state=0;
                  else if (theToken.equals("table"))state=1;
                  else {
                        if (state==0)create.add(theToken);
                        if (state==1)table.add(theToken);
                  }
            }
            String [] tableitem = table.toArray(new String[0]);
            for(int i = 0; i< table.size(); i++)
            {
                tableitem[i] = Utils.replaceAll(tableitem[i], "(", "");
                tableitem[i] = Utils.replaceAll(tableitem[i], ")", "");
            }
            for(int i = 0; i< table.size(); i++)
            {
                System.out.println(tableitem[i]);
            }
            
            //here the vectors select, from and where have what you are looking for
            System.out.println("NR of TOKEN IN Create:"+ create.toString());
            System.out.println("NR of TOKEN IN Table:"+table.toString());
            
            String tableName = "";
            //String pkName = null;
            ArrayList<String> pkName = new ArrayList<>();
            ArrayList<String> colsset = new ArrayList<>();
            ArrayList<String> typesset = new ArrayList<>();
            //String[] syntax_arr = command.split(" ");
            tableName = tableitem[0];
            System.out.println(tableName);
            Table tb = new Table(dbName, tableName);
            int i_pkey = 0;
            for(int i = 0; i < table.size(); i++)
            {
                if(tableitem[i].compareTo("primary") == 0)
                {
                    i_pkey = i;
                    System.out.println("" +i_pkey +"");
                }
                System.out.println("" +i_pkey +"");
            }
            if(i_pkey != 0)
            {
                for(int i = 1; i < i_pkey; i++)
                {
                    if(i%2 == 1)
                    {
                        colsset.add(tableitem[i]);
                    }
                    if(i%2 == 0)
                    {
                        typesset.add(tableitem[i]);  
                    }
                }
                for(int i = i_pkey + 2; i < table.size(); i++)
                {
                        pkName.add(tableitem[i]);
                }
                System.out.println(colsset.toString());
                System.out.println(typesset.toString());
                System.out.println(pkName.toString());
            }
            else
            {
                for(int i = 1; i < table.size(); i++)
                {
                    if(i%2 == 1)
                    {
                        colsset.add(tableitem[i]);
                    }
                    if(i%2 == 0)
                    {
                        typesset.add(tableitem[i]);  
                    }
                }
                System.out.println(colsset.toString());
                System.out.println(typesset.toString());
                System.out.println(pkName.toString());
                
            }
                
            return CreateTable(tb, colsset.toArray(new String[0]), typesset.toArray(new String[0]), pkName.toArray(new String[0]));
	}
	//
	public static int CreateTable(Table tb, String[] colsset, String[] typesset, String[]  pkName) {
		if (tb == null || tb.getTbName() == "" || tb.getDbName() == "")
			return MetaFile.ErrorInvalidAgrument;
		Document doc = MetaFile.LoadDoc();
		if (doc == null)
			return MetaFile.ErrorLoading;
		if (!CreateDatabase.CheckDatabaseExist(tb.getDbName()))
			return MetaFile.ErrorDBNotFound;
		if (CheckTableExist(tb.getDbName(), tb.getTbName()))
			return MetaFile.ErrorExisted;
		try {
			XPath xpath = XPathFactory.newInstance().newXPath();
			XPathExpression expr = xpath.compile("//Database[@Name='"
					+ tb.getDbName() + "']/Tables");

			Element dbNode = (Element) expr.evaluate(doc, XPathConstants.NODE);
			Element newTBNode = doc.createElement("Table");

			Attr attr = doc.createAttribute("Name");
			attr.setValue(tb.getTbName());
			newTBNode.setAttributeNode(attr);

			Element cls = doc.createElement("Columns");
			newTBNode.appendChild(cls);
			
			if(colsset != null)
			{
				int lenght = colsset.length;
				for(int i = 0; i < lenght; i++)
				{
					Element cl = doc.createElement("Column");
					cls.appendChild(cl);
					Attr attrchildname = doc.createAttribute("Name");
					attrchildname.setValue(colsset[i]);
                                        
					cl.setAttributeNode(attrchildname);
					Attr attrchildtype = doc.createAttribute("Type");
					attrchildtype.setValue(typesset[i]);
					cl.setAttributeNode(attrchildtype);
				}
			}
			
			Element cons = doc.createElement("Constraints");
                        newTBNode.appendChild(cons);
                        if(pkName != null)
                        {
                            for(int i = 0; i < pkName.length; i++)
                            {
                                Element con = doc.createElement("contraint");
                                cons.appendChild(con);

                                Attr attrContraintType = doc.createAttribute("Type");
                                attrContraintType.setValue("Primary Key");
                                con.setAttributeNode(attrContraintType);


                                    Attr attrContraintCol = doc.createAttribute("ContraintCol");
                                    attrContraintCol.setValue(pkName[i]);
                                    con.setAttributeNode(attrContraintCol);


                                Attr attrContraintName = doc.createAttribute("Name");
                                attrContraintName.setValue("pk_" + tb.getTbName());
                                con.setAttributeNode(attrContraintName);
                            }
                        }
                        
			dbNode.appendChild(newTBNode);
			if (MetaFile.Save(doc) == MetaFile.Success) {
				return MetaFile.Success;
			} else {
				return MetaFile.ErrorUnknown;
			}
		} catch (Exception e) {
			return MetaFile.ErrorUnknown;
		}
	}

	public static Table getTableByName(String dbName, String tbName) {
		try {
			Document doc = MetaFile.LoadDoc();
			XPath xpath = XPathFactory.newInstance().newXPath();
			// XPath Query for showing all nodes value
			XPathExpression expr = xpath.compile("//Database[@Name='" + dbName
					+ "']/Tables/Table[@Name='" + tbName + "']");
			Node resultNode = (Node) expr.evaluate(doc, XPathConstants.NODE);
			if (resultNode != null) {
				Table result = new Table(dbName, tbName);
				return result;
			}
		} catch (Exception e) {
			return null;
		}
		return null;
	}

	public static List<Table> getAllTableInDB(String dbName) {
		try {
			Document doc = MetaFile.LoadDoc();
			XPath xpath = XPathFactory.newInstance().newXPath();
			XPathExpression expr = xpath.compile("//Database[@Name='" + dbName
					+ "']/Tables/Table");
			NodeList listNodes = (NodeList) expr.evaluate(doc,
					XPathConstants.NODESET);
			if (listNodes != null) {
				List<Table> result = new ArrayList<Table>();
				for (int i = 0; i < listNodes.getLength(); i++) {
					Element itemNode = (Element) listNodes.item(i);
					Table item = new Table(dbName, itemNode
							.getAttribute("Name"));
					result.add(item);
				}
				return result;
			}
		} catch (Exception e) {
			return null;
		}
		return null;

	}

	public static boolean CheckTableExist(String dbName, String tbName) {
		try {
			Document doc = MetaFile.LoadDoc();
			XPath xpath = XPathFactory.newInstance().newXPath();
			XPathExpression expr = xpath.compile("//Database[@Name='" + dbName
					+ "']/Tables/Table[@Name='" + tbName + "']");
			Node result = (Node) expr.evaluate(doc, XPathConstants.NODE);
			if (result != null)
				return true;
		} catch (Exception e) {
			return false;
		}
		return false;
	}
}
